package batch.flashgame;

import java.util.*;
import java.sql.*;
import java.util.Date;

import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.log4j.*;

import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;

import batch.common.db.*;
import batch.common.report.*;

public class FlashGame
{    
    private final String TODAY       = DateFormatUtils.format(new Date(), "yyyyMMdd");  //Get Current Date
    private final String MAILSUBJECT = "Flashgame Rank Fail(" + TODAY + ")";

    private String sMethodNM = "start";
   
    private StringBuffer sql = new StringBuffer();

    public FlashGame(){
        ;
    }
    
    public static void main(String[] args){
        FlashGame fg = new FlashGame();
        
        Logger.getLogger("flashgame.flashgame").info("******************* START ***********************");
        fg.start(args[0]);
        Logger.getLogger("flashgame.flashgame").info("******************* E N D ***********************");
    }

    public void start(String flag){
        StringBuffer error_rank = new StringBuffer();
        StringBuffer error_line = new StringBuffer();        
        Connection conn = null;
        
        try{
            conn = ConnectionManager.getConnection();
            conn.setAutoCommit(false);
            
            Vector vGameList = getGameList(conn);
    
            String point_cd = "";
            int point_nm = 0;
    
            if(flag.equals("W")){
                point_cd = "00701";
                point_nm = 100;
            }
            else if(flag.equals("M")){
                point_cd = "00702";
                point_nm = 500;
            }
            else{
                System.exit(0);
            }
    
            int vCnt = (vGameList == null) ? 0 : vGameList.size();
            
            for(int i=0; i<vCnt; i++){
                String[] game = (String[])vGameList.elementAt(i);
                Vector vRankList = getRank(conn, flag, game[1], game[0]);
                int vCnt2 = (vRankList == null)? 0 : vRankList.size();
                
                for(int j=0; j<vCnt2; j++){
                    String[] rank = (String[])vRankList.elementAt(j);
                    

                    if(insertRank(conn, rank)){
                        //������ ���� ���� - ������ ������ point history �� user_no �� �ѱ��.
                        boolean pointFlag  = false;
    
                        if( game[1].equals("gbr") || game[1].equals("ita") || game[1].equals("ger") || game[1].equals("esp") ||
                            game[1].equals("rus") || game[1].equals("uan") || game[1].equals("zaf") || game[1].equals("bel") ||
                            game[1].equals("nld") || game[1].equals("prt") || game[1].equals("nrd") ){
                            pointFlag = updatePoint(conn, Long.parseLong(rank[3]) , point_cd, rank[4], point_nm);
                        }else{
                            pointFlag = updatePoint(conn, rank[2], point_cd, rank[4], point_nm);
                        }
    
                        if(!pointFlag){
                        	error_rank.append("point||"+rank[0]+"||"+rank[1]+"||"+rank[2]+"||"+rank[3]+"||"
                        					+rank[4]+"||"+rank[5]+"||"+rank[6]+"||"+rank[7]+"||"+rank[8]+"\n");
                            
                            throw new Exception("updatePoint Error");
                        }
                    }else{
                    	 error_rank.append("rank||"+rank[0]+"||"+rank[1]+"||"+rank[2]+"||"+rank[3]+"||"
                        					+rank[4]+"||"+rank[5]+"||"+rank[6]+"||"+rank[7]+"||"+rank[8]+"\n");
                    	 
                        throw new Exception("rank Insert Error");
                    }
                    
                }
            }
            conn.commit();
        }
        catch(SQLException sqlex){
            if(conn != null) try { conn.rollback(); } catch(Exception se) { se.printStackTrace(); }
            
            sqlex.printStackTrace();
            
            Logger.getLogger("flashgame.flashgame").info(flag + " Occurred Error in " + sMethodNM);
            
            Logger.getLogger("flashgame.flashgame").info(sqlex.toString());
            
            StackTraceElement[] stElement = sqlex.getStackTrace();
            for(int p=0; p<stElement.length;p++){
                Logger.getLogger("flashgame.flashgame").info(stElement[p].toString());
            }
            
            Logger.getLogger("flashgame.flashgame").info("================= SQL ==========================");
            Logger.getLogger("flashgame.flashgame").info(sql.toString());
            Logger.getLogger("flashgame.flashgame").info("================= SQL ==========================");
            
            Logger.getLogger("flashgame.error_rank").info(error_rank.toString());
            
            ReportError.byEmail(MAILSUBJECT, sqlex.toString());
            
        }
        catch(Exception ex){
            if(conn != null) try { conn.rollback(); } catch(Exception se) { se.printStackTrace(); }
            
            ex.printStackTrace();
            
            Logger.getLogger("flashgame.flashgame").info(flag + " Occurred Error in " + sMethodNM);
            
            Logger.getLogger("flashgame.flashgame").info(ex.toString());
               
            StackTraceElement[] stElement = ex.getStackTrace();
            for(int p=0; p<stElement.length;p++){
                Logger.getLogger("flashgame.flashgame").info(stElement[p].toString());
            }
            
            Logger.getLogger("flashgame.flashgame").info("================= SQL ==========================");
            Logger.getLogger("flashgame.flashgame").info(sql.toString());
            Logger.getLogger("flashgame.flashgame").info("================= SQL ==========================");
            
            Logger.getLogger("flashgame.error_rank").info(error_rank.toString());
            Logger.getLogger("flashgame.error_rank").info("@"+error_line.toString());
            
            ReportError.byEmail(MAILSUBJECT, ex.toString());
        }
        finally{
            if(conn != null) try { conn.close(); } catch(Exception ex) { ex.printStackTrace(); }
        }       
    }

    public Vector getGameList(Connection conn) throws Exception {
        sMethodNM = "getGameList";
        
        Statement stmt = null;
        ResultSet rs = null;

        Vector vGameList = new Vector();
        
        sql.setLength(0);
        sql.append("SELECT    GAME_ID            \n")
           .append("     ,    SITE_CD            \n") 
           .append("  FROM    G_CO_FLASHGAME_MST \n") 
           .append(" WHERE    VISIBLE_CD = 'A'   \n") 
           .append(" GROUP BY SITE_CD            \n")
           .append("        , GAME_ID            \n");

        try{
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql.toString());
            
            while(rs.next()){
                String[] arrList = new String[2];
                arrList[0] = rs.getString(1);
                arrList[1] = rs.getString(2);
                vGameList.addElement(arrList);
            }
        }
        finally{
            if(rs != null)   try { rs.close();   } catch(Exception ex){}
            if(stmt != null) try { stmt.close(); } catch(Exception ex){}
        }
        
        return vGameList;
    }

    public Vector getRank(Connection conn, String flag, String site_cd, String game_cd) throws Exception {
        sMethodNM = "getRank";
        
        Statement stmt = null;
        ResultSet rs = null;
        
        Vector vGameRank = new Vector();
        
        sql.setLength(0);
        
        if(flag.equals("W")){
        	sql.append("SELECT ROWNUM                                                               	\n")
               .append("     , GAME_ID                                                               	\n")
               .append("     , USER_ID                                                               	\n");
        	if( site_cd.equals("gbr") || site_cd.equals("ita") || site_cd.equals("ger") || 
				site_cd.equals("esp") || site_cd.equals("uan") || site_cd.equals("zaf") || 
				site_cd.equals("bel") || site_cd.equals("nld") || site_cd.equals("prt") ){
            sql.append("     , USER_NO                    												\n");
            }else{
            sql.append("     , GET_USER_NO(USER_ID, '" + site_cd + "') AS USER_NO                    	\n");
            }
            sql.append("     , '" + site_cd + "' SITE_CD                                             	\n")
               .append("     , SCORE_NUM                                                             	\n")
               .append("     , LEVEL_NUM                                                             	\n")
               .append("     , '" + flag + "' RANKING_CD                                             	\n")
               .append("     , RANKING_YMD                                                           	\n")
               .append("  FROM (SELECT    GAME_ID                                                    	\n")
               .append("             ,    USER_ID                                                    	\n");
            if( site_cd.equals("gbr") || site_cd.equals("ita") || site_cd.equals("ger") || 
				site_cd.equals("esp") || site_cd.equals("uan") || site_cd.equals("zaf") || 
				site_cd.equals("bel") || site_cd.equals("nld") || site_cd.equals("prt") ){
            sql.append("     		 , 	  USER_NO                    									\n");
            }
            sql.append("             ,    SCORE_NUM                                                  	\n")
               .append("             ,    LEVEL_NUM                                                  	\n")
               .append("             ,    WEEK_YMD                                                   	\n")
               .append("             ,    SUBSTR(NVL(MODIFY_YMDHMS,CREATED_YMDHMS),1,8) RANKING_YMD  	\n")
               .append("          FROM    G_" + site_cd + "_PL_FLASHGAME_SCORE                       	\n")
               .append("         WHERE    GAME_ID  = '" + game_cd + "'                               	\n")
               .append("           AND    WEEK_YMD = TO_CHAR(TRUNC(sysdate -7,'DAY'),'YYYYMMDD')     	\n")
               .append("         ORDER BY SCORE_NUM    DESC                                          	\n")
               .append("                , RANKING_YMD  ASC)                                          	\n")
               .append(" WHERE ROWNUM BETWEEN 1 AND 5                                                	\n");
        }      
        else{  
        	sql.append("SELECT ROWNUM                                                                                                 	\n")
               .append("     , GAME_ID                                                                                                	\n")
               .append("     , USER_ID                                                                                                	\n");
            if( site_cd.equals("gbr") || site_cd.equals("ita") || site_cd.equals("ger") || 
				site_cd.equals("esp") || site_cd.equals("uan") || site_cd.equals("zaf") || 
				site_cd.equals("bel") || site_cd.equals("nld") || site_cd.equals("prt") ){
            sql.append("     , USER_NO                    																				\n");
            }else{
            sql.append("     , GET_USER_NO(USER_ID, '" + site_cd + "') AS USER_NO                    									\n");
            }
            sql.append("     , SITE_CD                                                                                                	\n")
               .append("     , SCORE_NUM                                                                                              	\n")
               .append("     , LEVEL_NUM                                                                                              	\n")
               .append("     , 'M' RANKING_CD                                                                                         	\n")
               .append("     , RANKING_YMD                                                                                            	\n")
               .append("  FROM(SELECT    GAME_ID                                                                                      	\n")
               .append("            ,    USER_ID                                                                                      	\n");
           if( site_cd.equals("gbr") || site_cd.equals("ita") || site_cd.equals("ger") || 
				site_cd.equals("esp")|| site_cd.equals("uan") || site_cd.equals("zaf") || 
				site_cd.equals("bel")|| site_cd.equals("nld") || site_cd.equals("prt")){
           sql.append("     	   ,	USER_NO                    																		\n");
           }
           sql.append("            ,    '" + site_cd + "' SITE_CD                                                                    	\n")
              .append("            ,    SCORE_NUM                                                                                    	\n")
              .append("            ,    LEVEL_NUM                                                                                    	\n")
              .append("            ,    SUBSTR(NVL(MODIFY_YMDHMS, CREATED_YMDHMS), 1, 8) RANKING_YMD                                 	\n")
              .append("         FROM    G_" + site_cd + "_PL_FLASHGAME_SCORE                                                         	\n")
              .append("        WHERE    GAME_ID = '" + game_cd + "'                                                                  	\n")
              .append("          AND    SUBSTR(NVL(MODIFY_YMDHMS,CREATED_YMDHMS), 1, 6) = TO_CHAR(ADD_MONTHS(sysdate, -1), 'yyyymm')	\n")
              .append("        ORDER BY SCORE_NUM    DESC                                                                            	\n")
              .append("               , RANKING_YMD  ASC)                                                                            	\n")
              .append(" WHERE ROWNUM = 1                                                                                             	\n");
        }      
               
        try{
            stmt = conn.createStatement();
            Logger.getLogger("flashgame.flashgame").info("================= query ==========================");
            Logger.getLogger("flashgame.flashgame").info(sql.toString());
            Logger.getLogger("flashgame.flashgame").info("================= query ==========================");
            rs = stmt.executeQuery(sql.toString());
            
            while(rs.next()){
                String[] arrList = new String[9];
                arrList[0] = rs.getString(1); //ROWNUM
                arrList[1] = rs.getString(2); //GAME_ID
                arrList[2] = rs.getString(3); //USER_ID
                arrList[3] = rs.getString(4); //USER_NO
                arrList[4] = rs.getString(5); //SITE_CD
                arrList[5] = rs.getString(6); //SCORE_NUM
                arrList[6] = rs.getString(7); //LEVEL_NUM
                arrList[7] = rs.getString(8); //RANKING_CD
                arrList[8] = rs.getString(9); //RANKING_YMD
       
                vGameRank.addElement(arrList);
            }
        }
        finally{
            if(rs != null)   try { rs.close();   } catch(Exception ex){}
            if(stmt != null) try { stmt.close(); } catch(Exception ex){}
        }  
        
        return vGameRank;
    }          
               
    public boolean insertRank(Connection conn, String[] rank) throws Exception {
        sMethodNM = "insertRank";
        
        PreparedStatement pstmt = null;
        
        boolean result = false;
        
        sql.setLength(0);
        sql.append("INSERT INTO G_CO_FLASHGAME_BEST(RANKING_NUM  \n")
           .append("                              , GAME_ID      \n")
           .append("                              , USER_ID      \n")
           .append("                              , USER_NO      \n")
           .append("                              , SITE_CD      \n")
           .append("                              , SCORE_NUM    \n")
           .append("                              , LEVEL_NUM    \n")
           .append("                              , RANKING_CD   \n")
           .append("                              , RANKING_YMD) \n") 
           .append("                        VALUES( ?            \n")
           .append("                              , ?            \n")
           .append("                              , ?            \n")
           .append("                              , ?            \n")
           .append("                              , ?            \n")
           .append("                              , ?            \n")
           .append("                              , ?            \n")
           .append("                              , ?            \n")
           .append("                              , ?)           \n");
           
        try{
            pstmt = conn.prepareStatement(sql.toString());
            
            pstmt.setString( 1, rank[0]);  //RANKING_NUM
            pstmt.setString( 2, rank[1]);  //GAME_ID
            pstmt.setString( 3, rank[2]);  //USER_ID
            pstmt.setString( 4, rank[3]);  //USER_NO
            pstmt.setString( 5, rank[4]);  //SITE_CD
            pstmt.setString( 6, rank[5]);  //SCORE_NUM
            pstmt.setString( 7, rank[6]);  //LEVEL_NUM
            pstmt.setString( 8, rank[7]);  //RANKING_CD
            pstmt.setString( 9, rank[8]);  //RANKING_YMD
            
            pstmt.executeUpdate();
            
            result = true;
        }
        finally{
            if(pstmt != null) try { pstmt.close(); } catch(Exception ex){}
        }      
        
        return result;
    }          
               
    public boolean updatePoint(Connection conn, String user_id, String point_code, String site_cd, int point_nm) throws Exception {
        sMethodNM = "updatePoint_old";
        
        boolean result = false;
        
        sql.setLength(0);
        sql.append(" call  PointCountry_Pack.ChangePoint_MAIN(?,?,?,?,?,?) ");
        
        CallableStatement cs = null;
        
        try{
            cs = conn.prepareCall(sql.toString());
            cs.setString(1, user_id);
            cs.setString(2, point_code);
            cs.setString(3, site_cd);
            cs.setInt(4, point_nm);
            cs.setInt(5, 0);
            cs.registerOutParameter(6, 4);
            cs.execute();
            
            int i = cs.getInt(6);
            
            if(i == 0){
                result =  true;
            }
        }
        finally{
            if(cs != null) try { cs.close(); } catch(Exception e){}
        }
              
        return result;
    }          
               
    public boolean updatePoint(Connection conn, long user_no, String point_code, String site_cd, int point_nm) throws Exception {
        sMethodNM = "updatePoint_new";
        
        boolean result = false;
        
        sql.setLength(0);
        sql.append(" call  changepoint_pack.ChangePoint_MAIN(?,?,?,?,?,?) ");
        
        CallableStatement cs = null;
            
        try{
            cs = conn.prepareCall(sql.toString());
            cs.setLong(1, user_no);
            cs.setString(2, point_code);
            cs.setString(3, site_cd);
            cs.setInt(4, point_nm);
            cs.setInt(5, 0);
            cs.registerOutParameter(6, 4);
            cs.execute();
            
            int i = cs.getInt(6);
            
            if(i == 0){
                result =  true;
            }
        }
        finally{
            if(cs != null) try { cs.close(); } catch(Exception e){}
        }
              
        return result;
    }
}              
